package com.nstechs.commerce.transaction.saleplan;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.nstechs.commerce.common.util.DBUtil;

public class SalePlanTransfer {
	private static Logger logger = LoggerFactory.getLogger("SEVERE");
	private LogData logData;
	private DBUtil dbutilInsert;
	private DBUtil dbutilSelect;

	public static void main(String[] args) {
		SalePlanTransfer salePlanTransfer = new SalePlanTransfer();
		salePlanTransfer.inital();;
		salePlanTransfer.createTempTable();
//		salePlanTransfer.insertSalePlanData();
//		salePlanTransfer.updateSalePlan();
//		salePlanTransfer.insertSalePriceData();
//		salePlanTransfer.insertProductSaleData();
	}
	
	public void dataTransfer() {
		SalePlanTransfer salePlanTransfer = new SalePlanTransfer();
		salePlanTransfer.inital();;
		salePlanTransfer.createTempTable();
		salePlanTransfer.insertSalePlanData();
		salePlanTransfer.updateSalePlan();
		salePlanTransfer.insertSalePriceData();
		salePlanTransfer.insertProductSaleData();
	}
	
	public DBUtil getdbutilInsert() {
		return dbutilInsert;
	}

	public DBUtil getdbutilSelect() {
		return dbutilSelect;
	}

	public void inital() {
		Properties properties = new Properties();
		try {
			properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("transaferjdbc.properties"));
			this.dbutilSelect = new DBUtil(properties.getProperty("jdbc.source.url"), properties.getProperty("jdbc.source.user"), properties.getProperty("jdbc.source.password"));
			this.dbutilInsert = new DBUtil(properties.getProperty("jdbc.target.url"), properties.getProperty("jdbc.target.user"), properties.getProperty("jdbc.target.password"));
		} catch (IOException e) {
			logger.error(e.getMessage());
		}
		this.logData = new LogData();
	}

	public void createTempTable() {
		Connection createConn = dbutilInsert.getConnection();
		Connection getConn = dbutilSelect.getConnection();
		String createBrandTempSql = "CREATE TABLE brand_temp ( id INT PRIMARY KEY AUTO_INCREMENT, import_brand_id INT );";

		String insertTempBrandSql = "insert into brand_temp (id,import_brand_id) values(?,?)";

		String queryBrandSql = "select id from brand where status = 'A'";

		PreparedStatement pstmBrand = null;
		List<Integer> brandIds = new ArrayList<Integer>();
		System.out.println("Step Start!");
		try {
			PreparedStatement perpCreateBrand = getConn
					.prepareStatement(createBrandTempSql);
			perpCreateBrand.execute();
			pstmBrand = getConn.prepareStatement(queryBrandSql);
			ResultSet rsBrand = pstmBrand.executeQuery();
			while (rsBrand.next()) {
				brandIds.add(rsBrand.getInt("id"));
			}
			for (Integer id : brandIds) {
				PreparedStatement perpInsert = getConn
						.prepareStatement(insertTempBrandSql);
				perpInsert.setInt(1, id);
				perpInsert.setInt(2, id);
				perpInsert.execute();
				dbutilInsert.closeStatement(perpInsert);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbutilSelect.closeStatement(pstmBrand);
			dbutilSelect.closeConnection(getConn);
			dbutilInsert.closeConnection(createConn);
			System.out.print("Step Over!");
		}
	}

	public void insertBrandData() {
		String selectSql = "select b.id,b.description,b.brand_name,if(b.on_brand_list=1,'Y','N'),b.uri_name,if(strcmp(b.status,'A')=0,'Y','N') as status "
				+ "from brand b " + "where b.status ='A';";
		String insertSql = "insert into brand (id,description, name, onbrandslist, short_name, status) values (?,?,?,?,?,?);";
		Connection selectConn = dbutilSelect.getConnection();
		Connection insertConn = dbutilInsert.getConnection();
		PreparedStatement pstm = null;
		PreparedStatement perpInsert = null;
		List<String> col1 = new ArrayList<String>();
		List<String> col2 = new ArrayList<String>();
		List<String> col3 = new ArrayList<String>();
		List<String> col4 = new ArrayList<String>();
		List<String> col5 = new ArrayList<String>();
		List<String> col6 = new ArrayList<String>();

		try {
			System.out.println("start select!");
			pstm = selectConn.prepareStatement(selectSql);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				col1.add(rs.getString(1));
				col2.add(rs.getString(2));
				col3.add(rs.getString(3));
				col4.add(rs.getString(4));
				col5.add(rs.getString(5));
				col6.add(rs.getString(6));
			}
			System.out.println("start insert!");
			insertConn.setAutoCommit(false);
			perpInsert = insertConn.prepareStatement(insertSql);
			for (int i = 0; i < col1.size(); i++) {
				perpInsert.setString(1, col1.get(i));
				perpInsert.setString(2, col2.get(i));
				perpInsert.setString(3, col3.get(i));
				perpInsert.setString(4, col4.get(i));
				perpInsert.setString(5, col5.get(i));
				perpInsert.setString(6, col6.get(i));
				perpInsert.execute();
			}
			insertConn.commit();
			logData.logBrand(selectConn);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbutilSelect.closeStatement(pstm);
			dbutilInsert.closeStatement(perpInsert);
			dbutilSelect.closeConnection(selectConn);
			dbutilInsert.closeConnection(insertConn);
		}
	}

	public void insertSalePlanData() {
		String selectSql = "select ps.id,ps.created_by,ps.created_date,ps.description,ps.end_date,ps.`name`,ps.seo_keywords,ps.start_date,if(strcmp(ps.status,'A')=0,'Y','N') as status "
				+ "from product_sale ps " + "where ps.status in('A','I');";
		String insertSql = "insert into saleplan (id,create_by, created_date,description,end_date,name,seo_keywords,start_date,status) values (?,?,?,?,?,?,?,?,?);";
		Connection selectConn = dbutilSelect.getConnection();
		Connection insertConn = dbutilInsert.getConnection();
		PreparedStatement pstm = null;
		PreparedStatement perpInsert = null;

		List<String> col1 = new ArrayList<String>();
		List<String> col2 = new ArrayList<String>();
		List<String> col3 = new ArrayList<String>();
		List<String> col4 = new ArrayList<String>();
		List<String> col5 = new ArrayList<String>();
		List<String> col6 = new ArrayList<String>();
		List<String> col7 = new ArrayList<String>();
		List<String> col8 = new ArrayList<String>();
		List<String> col9 = new ArrayList<String>();

		try {
			System.out.println("start product_sale select!");
			pstm = selectConn.prepareStatement(selectSql);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				col1.add(rs.getString(1));
				col2.add(rs.getString(2));
				col3.add(rs.getString(3));
				col4.add(rs.getString(4));
				col5.add(rs.getString(5));
				col6.add(rs.getString(6));
				col7.add(rs.getString(7));
				col8.add(rs.getString(8));
				col9.add(rs.getString(9));
			}
			System.out.println("start sale_plan insert!");
			insertConn.setAutoCommit(false);
			perpInsert = insertConn.prepareStatement(insertSql);
			for (int i = 0; i < col1.size(); i++) {
				perpInsert.setString(1, col1.get(i));
				perpInsert.setString(2, col2.get(i));
				perpInsert.setString(3, col3.get(i));
				perpInsert.setString(4, col4.get(i));
				perpInsert.setString(5, col5.get(i));
				perpInsert.setString(6, col6.get(i));
				perpInsert.setString(7, col7.get(i));
				perpInsert.setString(8, col8.get(i));
				perpInsert.setString(9, col9.get(i));
				perpInsert.execute();
			}
			insertConn.commit();
			logData.logProductSale(selectConn);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbutilSelect.closeStatement(pstm);
			dbutilInsert.closeStatement(perpInsert);
			dbutilSelect.closeConnection(selectConn);
			dbutilInsert.closeConnection(insertConn);
		}
	}

	public void insertSalePriceData() {
		String selectSql = "select psi.id,psi.sku,psi.product_sale_id "
				+ "from products.product_sale_item psi ";
		// "INNER JOIN products.productsale_temp pt "+
		// "ON psi.product_sale_id = pt.import_productsale_id "+
		// "INNER JOIN productsaleitem_temp pit "+
		// "on psi.id = pit.import_productsaleitem_id;";
		String insertSql = "insert into saleprice (id,sku,sale_plan_id) values (?,?,?);";
		Connection selectConn = dbutilSelect.getConnection();
		Connection insertConn = dbutilInsert.getConnection();
		PreparedStatement pstm = null;
		PreparedStatement perpInsert = null;

		List<String> col1 = new ArrayList<String>();
		List<String> col2 = new ArrayList<String>();
		List<String> col3 = new ArrayList<String>();

		try {
			System.out.println("start product_sale_item select!");
			pstm = selectConn.prepareStatement(selectSql);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				col1.add(rs.getString(1));
				col2.add(rs.getString(2));
				col3.add(rs.getString(3));
			}
			System.out.println("start saleprice insert!");
			insertConn.setAutoCommit(false);
			perpInsert = insertConn
					.prepareStatement("SET FOREIGN_KEY_CHECKS=0");
			perpInsert.execute();
			perpInsert = insertConn.prepareStatement(insertSql);
			for (int i = 0; i < col1.size(); i++) {
				perpInsert.setString(1, col1.get(i));
				perpInsert.setString(2, col2.get(i));
				perpInsert.setString(3, col3.get(i));
				perpInsert.addBatch();
				if (i % 1000 == 0) {
					perpInsert.executeBatch();
					perpInsert.clearBatch();
				}
			}
			perpInsert.executeBatch();
			insertConn.commit();
			insertConn.setAutoCommit(true);

		} catch (Exception e) {
			try {
				insertConn.rollback();
				insertConn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			dbutilSelect.closeStatement(pstm);
			dbutilInsert.closeStatement(perpInsert);
			dbutilSelect.closeConnection(selectConn);
			dbutilInsert.closeConnection(insertConn);
		}
	}

	public void insertProductSaleData() {
		String insertSql = "insert into productsale (brand_id,product_id,sale_plan_id) SELECT DISTINCT p.brand_id as brand_id, p.id as product_id,spa.id as sale_plan_id "
				+ "from product p "
				+ "INNER JOIN productitem pi "
				+ "on p.id = pi.product_id "
				+ "INNER JOIN saleprice sp "
				+ "on pi.sku = sp.sku "
				+ "INNER JOIN saleplan spa "
				+ "on sp.sale_plan_id = spa.id;";
		Connection insertConn = dbutilInsert.getConnection();
		PreparedStatement perpInsert = null;
		System.out.println("start productsale insert!");
		try {
			insertConn.setAutoCommit(false);
			perpInsert = insertConn
					.prepareStatement("SET FOREIGN_KEY_CHECKS=0");
			perpInsert.execute();
			perpInsert = insertConn.prepareStatement(insertSql);
			perpInsert.execute();
			insertConn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			System.out.println("productsale insert over!");
			dbutilInsert.closeStatement(perpInsert);
			dbutilInsert.closeConnection(insertConn);
		}
	}
	public void updateSalePlan(){
		String updateSql = "UPDATE `saleplan` SET `name` = replace (`name`, name ,SUBSTRING(name ,1, LENGTH(name)-11)) WHERE name REGEXP '[0-9]{2}-[0-9]{2}-[0-9]{4}$';";
		Connection insertConn = dbutilInsert.getConnection();
		PreparedStatement perpInsert = null;
		try {
			insertConn.setAutoCommit(false);
			perpInsert = insertConn.prepareStatement(updateSql);
			perpInsert.execute();
			insertConn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			dbutilInsert.closeStatement(perpInsert);
			dbutilInsert.closeConnection(insertConn);
		}
	}
}
